ALTER      proc sp_get_all_lot_numbers
@process_step_id int,
@location_Id int
as

declare @lot_Id int
declare @lot_number varchar(50)
declare @status varchar(20)
declare @results Table(LotID varchar(20),LotNumber varchar(50),Status varchar(50))

	declare lotCursor CURSOR FOR
	select lot_id,lot_number,lot_current_status from lot_table where ProcessStep_ID = @process_step_id and Location_ID = @location_Id and lot_number <> '' and lot_current_status = 'Running'

	open lotCursor 
		fetch next from lotCursor into @lot_Id,@lot_number,@status
		while @@fetch_status = 0
		begin
			insert into @results (LotID,LotNumber,Status) values (@lot_Id,@lot_number,@status)
			set @lot_Id = null
			set @lot_number = null
			set @status = null 
			fetch next from lotCursor into @lot_Id,@lot_number,@status
		end
	close lotCursor
	deallocate lotCursor

	select LotID, LotNumber, Status from @results

